<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

$currentDir = getcwd();

if (strpos($currentDir, 'batcave') !== false) {
    $host = "fdb3.batcave.net";
    $dbname = "1540372_space";
    $username = "1540372_space";
    $password = "gribpassword2";
} else if (strpos($currentDir, 'wamp') !== false) {
    $username = "root";
    $password = "";
    $host = "127.0.0.1";
    $dbname = "metocSpace";
} else if (strpos($currentDir, '7359596') !== false) {
    //godaddy path
    // /home/content/96/7359596/html/metocSpaceTesting or /home/content/96/7359596/html/metocSpace
    if (strpos($currentDir, 'metocSpaceTesting') !== false) {
        $host = "68.178.217.7"; //server via TCP/IP
        $dbname = "metocSpaceTest";
        $username = "metocSpaceTest";
        $password = "gribPassword#1";
    } else {
        $host = "68.178.217.4";
        $dbname = "metocspace";
        $username = "metocspace";
        $password = "gribPassword#1";
    }
}
//VM at data center
else {
    $username = "root";
    $host = "127.0.0.1";
    $dbname = "metocspace";
    $password = "gribPassword#1";
}

//CUSTOM************************************************************************

function DuplicateMySQLRecord($table, $id_field, $id, $wbsID = NULL) {
    // load the original record into an array  
    $result = executeSelect("SELECT * FROM $table WHERE $id_field='$id'");
    $original_record = mysqli_fetch_assoc($result);
    $multiyearID = $original_record['multiyearProjectID'];
    // insert the new record and get the new auto_increment id
    if ($wbsID) {
        $newid = executeInsert("INSERT INTO $table ($id_field, wbsID, multiyearProjectID) VALUES (NULL,$wbsID, $multiyearID)");
    } else {
        $newid = executeInsert("INSERT INTO $table ($id_field) VALUES (NULL)");
    }
    //$newid = $mysqli->insert_id;
    // generate the query to update the new record with the previous values
    $query = "UPDATE $table SET ";
    foreach ($original_record as $key => $value) {
        if ($key != $id_field) {
            $query .= '`' . $key . '` = "' . str_replace('"', '\"', $value) . '", ';
        }
    }
    $query = substr($query, 0, strlen($query) - 2); # lop off the extra trailing comma
    $query .= " WHERE {$id_field}={$newid}";

    echo $query;
    $updateResult = executeSelect($query);
    if ($updateResult) {
        return $newid;
    } else {
        return "Error";
    }
    //mysql_query($query);
    // return the new id
    //return $newid;
}

function doesProjectHaveActiveFeedbackComments($projectID) {
    $projectResult = executeSelect("SELECT n.* FROM notes n, project p WHERE p.projectID=$projectID AND p.wbsID=n.wbsId and type='feedback'");
    if ($projectResult) {
        while ($row = mysqli_fetch_array($projectResult)) {
            if (strcmp($row['status'], "completed") !== 0) {
                return true;
            }
        }
    }
    $taskResult = executeSelect("SELECT n.* FROM notes n, task t WHERE t.projectID=$projectID AND t.wbsID=n.wbsId and type='feedback'");
    if ($taskResult) {
        while ($row = mysqli_fetch_array($taskResult)) {
            if (strcmp($row['status'], "completed") !== 0) {
                return true;
            }
        }
    }
    $subtaskResult = executeSelect("SELECT n.* FROM notes n, task t, subtask st WHERE t.projectID=$projectID AND t.taskID=st.taskID AND st.wbsID=n.wbsId and type='feedback'");
    if ($subtaskResult) {
        while ($row = mysqli_fetch_array($subtaskResult)) {
            if (strcmp($row['status'], "completed") !== 0) {
                return true;
            }
        }
    }
    return false;
}

/**
 * 
 * @param type $table
 * @param type $idName
 * @param type $idValue
 * @param type $field
 * @return type
 */
function getFieldGivenAnID($table, $idName, $idValue, $field) {

    $result = executeSelect("SELECT $field FROM $table WHERE $idName=$idValue");
    $row = mysqli_fetch_array($result);
    return $row[$field];
}

function getWbsString($wbsID) {
    $projectResult = executeSelect("SELECT wbsString FROM wbs WHERE $wbsID=wbsID");
    $row = mysqli_fetch_array($projectResult);
    return $row['wbsString'];
}

function getProjectIDFromTaskID($taskID) {
    $projectResult = executeSelect("SELECT projectID FROM task WHERE taskID=$taskID");
    $row = mysqli_fetch_array($projectResult);
    return $row['projectID'];
}

function getSubtasksForProject($projectID) {
    $subtaskResult = executeSelect("SELECT st.title FROM task t , subtask st WHERE t.projectID=$projectID AND t.taskID=st.taskID");
    $array = array();
    while ($row = mysqli_fetch_array($subtaskResult)) {
        $subtaskTitle = $row['title'];
        array_push($array, $subtaskTitle);
    }
    array_push($array, "Other");
    return $array;
}

/**
 * Deletes data in the edr table and creates relationships with sat and sensors
 * @param type $array 2d array rows-->a single edr cols-->edrDescription, sensorName, satName
 */
function updateEdrs($array) {
    deleteAllData("edr");
    relateSensorSatEdr($array);
}

function updateParameters($array) {
    deleteAllData("parameter");
    populateParameterTable($array);
}

/**
 * Deletes data in the sensor table and creates a new sensor table
 * @param type $array 2d array rows-->a single edr cols-->edrDescription, sensorName, satName
 */
function updateSensors($array) {
    deleteAllData("sensor");
    populateSensorTable($array);
}

/**
 * Deletes data in the satellite table and creates a new satellite table
 * @param type $array 2d array rows-->a single edr cols-->edrDescription, sensorName, satName
 */
function updateSatellites($array) {
    deleteAllData("satellite");
    populateSatelliteTable($array);
}

function updateWbs($array) {
    deleteAllData("wbs");
    populateWbstable($array);
}

function populateWbstable($array) {
    $arraySize = sizeof($array);
    for ($i = 1; $i < $arraySize; $i++) {
        $rowSize = sizeof($array[$i]);
        $row = $array[$i];
        $level = $row[$rowSize - 1];
        echo 'Level: ' . $level . '<br>';
        $other = $row[$rowSize - 2];
        echo 'other: ' . $other . '<br>';
        $sensorID = getSensorID($row[$rowSize - 3]);
        echo 'Sensor: ' . $sensorID . '<br>';
        $satelliteID = getSatelliteID($row[$rowSize - 4]);
        echo 'Sat: ' . $satelliteID . '<br>';
        $parameter = $row[$rowSize - 5];
        echo 'Param: ' . $parameter . '<br>';
        $decription = $row[$rowSize - 6];
        echo 'Desc: ' . $decription . '<br>';
        $wbsID = $row[$level - 1];
        echo 'WBS: ' . $wbsID . '<br>';
        $sqlSelect = "SELECT edrID FROM edr WHERE satellite=$satelliteID AND sensor=$sensorID AND description=$parameter";
        $result = executeSelect($sqlSelect);
        if ($result) {
            $row = mysqli_fetch_array($result);
            $edrID = $row[0];
            $sqlInsert = "INSERT into wbs (wbsID,description,other,level,edrID) values 
                                          ($wbsID,$decription,$other,$level,$edrID)";
            if (!executeInsert($sqlInsert)) {
                echo "Error inserting sensor " . mysqli_error() . '<br>';
            }
            //sleep(1);
        } else {
            echo 'Error getting edrID';
        }
    }
}

/**
 * Populates the sensor table from the wmo exported csv file
 * @param type $array an array of sensors from www.wmo-sat.info/oscar/instruments
 */
function populateSensorTable($array) {
    //Id,Acronym,Full name,Space Agency,Instrument type,Satellites,Usage from,Usage to,Last update
    //column 1 = acronym
    //column 2 = name
    //column 3 = agency
    //column 4 = type
    //column 5 = satellites
    //column 6 = startDate
    //column 7 = endDate
    //column 8 = lastUpdated

    for ($i = 1; $i < sizeof($array); $i++) {
        $curValues = $array[$i];
        //$values = explode(",", $curLine);
        //echo "ACR: $curValues[1] Name: $curValues[2] Country: $curValues[3] <br>";
        //echo "Size: "+sizeof($curValues)+"<br>";
        //echo "$curValues[0] $curValues[1] $curValues[2] $curValues[3] $curValues[4] $curValues[5] $curValues[6]<br>";
        $acronym = $curValues[1];
        $sqlResult = executeSelect("SELECT * FROM sensor WHERE acronym='$acronym'");
        if (!$sqlResult || $sqlResult->num_rows === 0) {
            //echo "$acronym <br>";
            $name = str_replace("\"", " ", $curValues[2]);
            $agency = $curValues[3];
            //echo "in: $type <br>";
            $type = trimSensorType($curValues[4]);
            //echo "out: $type <br>";
            //$satellites = getSensorsFromCsvField($curValues[5]);
            $startDate = $curValues[6];
            $endDate = $curValues[7];
            //$lastUpdated = $curValues[8];
            $agencyId = getSpaceAgencyIdFromAcronym($agency, " ");
            $insertResult = executeInsert("INSERT into sensor (`acronym`, `name`, `spaceAgencyID`, `type`, `startDate`, `endDate`) 
                                                      values (\"$acronym\", \"$name\", \"$agencyId\", \"$type\", '$startDate', '$endDate')");
        }
    }
}

function populateProgrammeTable($array) {
    //Id, Acronym, Full name, startDate, endDate, Agencies, Satellites
    //column 1 = acronym
    //column 2 = name
    //column 3 = startDate
    //column 4 = endDate
    //column 5 = Agencies
    //column 6 = Satellites

    for ($i = 1; $i < sizeof($array); $i++) {
        $curValues = $array[$i];
        //$values = explode(",", $curLine);
        //echo "ACR: $curValues[1] Name: $curValues[2] Country: $curValues[3] <br>";
        //echo "Size: "+sizeof($curValues)+"<br>";
        //echo "$curValues[0] $curValues[1] $curValues[2] $curValues[3] $curValues[4] $curValues[5] $curValues[6]<br>";
        $acronym = $curValues[1];
        $sqlResult = executeSelect("SELECT * FROM satelliteProgramme WHERE acronym='$acronym'");

        //insert the programme if it does not exist
        if (!$sqlResult || $sqlResult->num_rows === 0) {
            //echo "$acronym <br>";
            $name = str_replace("\"", " ", $curValues[2]);
            $agencies = getSensorsFromCsvField($curValues[5], " ");
            //echo "in: $type <br>";
            //$type = trimSensorType($curValues[4]);
            //echo "out: $type <br>";
            //$satellites = getSensorsFromCsvField($curValues[5]);
            //$startDate = $curValues[6];
            //$endDate = $curValues[7];
            //$lastUpdated = $curValues[8];
            $insertResult = executeInsert("INSERT into satelliteprogramme (`acronym`, `name`) 
                                                      values (\"$acronym\", \"$name\")");

            $sqlResult = executeSelect("SELECT * FROM satelliteProgramme WHERE acronym='$acronym'");

            //insert the programme --> agency map fields
            $row = mysqli_fetch_array($sqlResult);
            $programmeID = $row['satelliteProgrammeID'];
            $arraySize = sizeof($agencies);
            for ($i = 0; $i < $arraySize; $i++) {
                echo "AGENCY=";
                echo $agencies[$i];
                echo "=<br>";
                $agencyId = getSpaceAgencyIdFromAcronym($agencies[$i], " ");
                $insert2Result = executeInsert("INSERT into satelliteprogrammespaceagencymap (`spaceAgencyID`, `satelliteProgrammeID`) 
                                                      values (\"$agencyId\", \"$programmeID\")");
            }
        }
    }
}

function trimSensorType($type) {
    // input = 01. Moderate-resolution optical imager
    //output = Moderate-resolution optical imager
    $dotPosition = strpos($type, ".");
    if ($dotPosition) {
        return substr($type, $dotPosition + 2);
    } else {
        return $type;
    }
}

/**
 * Creates the sensor table
 * @param type $array array of edrs (row[1] sensor)
 */
function populateSensorTableOld($array) {
    //column 0 = edr
    //column 1 = sensor
    //column 2 = sat
    $arraySize = sizeof($array);
    for ($i = 0; $i < $arraySize; $i++) {
        $rowSize = sizeof($array[$i]);
        $row = $array[$i];
        $sqlResult = executeSelect("SELECT * FROM sensor WHERE name='$row[1]'");
        if (!$sqlResult || $sqlResult->num_rows === 0) {
            $sqlInsert = "INSERT into sensor (name) values ('$row[1]')";
            if (!executeInsert($sqlInsert)) {
                echo "Error inserting sensor <br>";
            }
        }
        //sleep(1);
    }
}

function populateSpaceAgencies($agencyArray) {

    for ($i = 1; $i < sizeof($agencyArray); $i++) {
        $curValues = $agencyArray[$i];
        //$values = explode(",", $curLine);
        //echo "ACR: $curValues[1] Name: $curValues[2] Country: $curValues[3] <br>";
        $acronym = $curValues[1];
        $name = $curValues[2];
        $country = $curValues[3];
        $insertResult = executeInsert("INSERT into spaceagency (`acronym`, `name`, `country`) values (\"$acronym\", \"$name\", \"$country\")");
    }
}

/**
 * Creates the satellite table from the wmo oscar tool csv file and populates the satsensormap table with the sensors in each row
 * @param type $array array of satellite records from www.wmo-sat.info/oscar/satellites
 */
function populateSatelliteTable($array) {
    //column 0 = acronym
    //column 1 = launch
    //column 2 = End Of Life
    //column 3 = Programme
    //column 4 = agency
    //column 5 = Orbit
    //column 6 = altitude
    //column 7 = longitude
    //column 8 = Inclination
    //column 9 = ECT ?
    //column 10 = Status
    //column 11 = Sensors
    //column 12 = Last Updated
    for ($i = 1; $i < sizeof($array); $i++) {
        //for ($i = 1; $i < 5; $i++) {
        $curValues = $array[$i];
        //$values = explode(",", $curLine);
        //echo "ACR: $curValues[1] Name: $curValues[2] Country: $curValues[3] <br>";
        //echo "Size: "+sizeof($curValues)+"<br>";
        //echo "$curValues[0] $curValues[1] $curValues[2] $curValues[3] $curValues[4] $curValues[5] $curValues[6]<br>";
        $acronym = $curValues[0];
        //echo "$acronym ";
        $launch = $curValues[1];
        $eol = $curValues[2];
        //when creating csv file there is a ? at the beginning of the eol so just don't include if it is there
        if (strstr($eol, "?")) {
            $eol = substr($curValues[2], 1);
        }
        //$programmeID = getProgrammeIdFromName($curValues[3]);
        $programmeID = 349; //hard coded generic program. No time right now to link program and space agency
        //$agency = trim($curValues[4]);
        $status = getHardwareStatusIdFrom($curValues[10]);
        //echo "$acronym: $curValues[11] <br>";
        $sensors = getSensorsFromCsvField($curValues[11], "\n");
        //$agencyId = getSpaceAgencyIdFromAcronym($agency);
        //echo 'agency: ' . $agency . ' agencyID: ' . $agencyId . ' acronym: '.$acronym.'<br>';
        //$insertResult = executeInsert("INSERT into satellite (`acronym`, `programme`, `launchDate`, `eol`, `spaceAgenctID`) 
        //                                             values (\"$acronym\", \"$programme\", \"$eol\", \"$agencyId\")");
        //to do parse launch and eol into date formats for mysql

        $satResult = executeSelect("SELECT * FROM satellite WHERE acronym='$acronym'");
        if (!$satResult || $satResult->num_rows === 0) {
            $insertResult = executeInsert("INSERT into satellite (`acronym`, `satelliteProgrammeID`, `hardwareStatusID`, `launchDate`) 
                                                    values (\"$acronym\", \"$programmeID\", \"$status\", \"$launch\")");
//
//        if ($insertResult) {
//            //now associate the sensors
            for ($j = 0; $j < sizeof($sensors); $j++) {
                $curSensor = $sensors[$j];
                echo "Acr: **$curSensor** <br>";
                $sensorID = getSensorIDFromAcronym($curSensor);
                //echo "ID: **$sensorID** <br>";
                //echo "Acr: **$acronym** <br>";
                $satID = getSatelliteIDFromAcronym($acronym);
                //echo "ID: **$satID** <br>";
                //does this relationship already exist?
                $satSensorResult = executeSelect("SELECT * FROM satsensormap WHERE satelliteID=$satID AND sensorID=$sensorID");
                if ($satSensorResult && $satSensorResult->num_rows === 0) {
                    $insertResult = executeInsert("INSERT into satsensormap (`satelliteID`, `sensorID`) values ($satID, $sensorID)");
                }
            }
        }
//        }
    }
}

function getSensorsFromCsvField($values, $delimeter) {
    $sensorArray = explode($delimeter, $values);
    echo "Num Sensors:";
    echo sizeof($sensorArray);
    echo "<br>";
    return $sensorArray;
}

/**
 * Creates the satellite table
 * @param type $array array of edrs (row[2] satellite)
 */
function populateSatelliteTableOld($array) {
    //column 0 = edr
    //column 1 = sensor
    //column 2 = sat
    $arraySize = sizeof($array);
    for ($i = 0; $i < $arraySize; $i++) {
        $rowSize = sizeof($array[$i]);
        $row = $array[$i];
        $sqlResult = executeSelect("SELECT * FROM satellite WHERE name='$row[2]'");
        if (!$sqlResult || $sqlResult->num_rows === 0) {
            $sqlInsert = "INSERT into satellite (name) values ('$row[2]')";
            if (!executeInsert($sqlInsert)) {
                echo "Error inserting satellite <br>";
            }
        }
        //sleep(1);
    }
}

function populateParameterTable($array) {
    //column 0 = parameter
    //column 1 = sensor
    //column 2 = sat
    $arraySize = sizeof($array);
    for ($i = 0; $i < $arraySize; $i++) {
        $rowSize = sizeof($array[$i]);
        $row = $array[$i];
        $sqlResult = executeSelect("SELECT * FROM parameter WHERE name='$row[0]'");
        if (!$sqlResult || $sqlResult->num_rows === 0) {
            $sqlInsert = "INSERT into parameter (name) values ('$row[0]')";
            if (!executeInsert($sqlInsert)) {
                echo "Error inserting parameter <br>";
            }
        } else {
            echo "nope SELECT * FROM parameter WHERE name='$row[0]' <br>";
        }
        //sleep(1);
    }
}

/**
 * Creates relationships with sat and sensors
 * @param type $array 2d array rows-->a single edr cols-->edrDescription, sensorName, satName
 */
function relateSensorSatEdr($array) {
    //assume these 
    //column 0 = edr description
    //column 1 = sensor
    //column 2 = sat
    $arraySize = sizeof($array);
    for ($i = 0; $i < $arraySize; $i++) {
        $rowSize = sizeof($array[$i]);
        $row = $array[$i];

        //get the sensor id for the sensor (name) in this row
        $sqlSelect = "SELECT sensorID FROM sensor WHERE name='$row[1]'";
        $result = executeSelect($sqlSelect);
        if ($result) {
            $temp = mysqli_fetch_assoc($result);
            $sensorID = $temp['sensorID'];
            //echo "sensorID ".$sensorID.'<br>';
            //get the satellite id
            $sqlSelect = "SELECT satelliteID FROM satellite WHERE name='$row[2]'";
            $result = executeSelect($sqlSelect);
            if ($result) {
                $temp = mysqli_fetch_assoc($result);
                $satelliteID = $temp['satelliteID'];
                $parameterName = $row[0];
                $paramIdResult = executeSelect("SELECT parameterID FROM parameter WHERE name='$parameterName'");
                $row = mysqli_fetch_array($paramIdResult);
                $paramID = $row['parameterID'];

                //query if this sat/sensor combination exists in the satSensorMap
                $satSensorResult = executeSelect("SELECT * FROM satsensormap WHERE satelliteID=$satelliteID AND sensorID=$sensorID");

                //it does not exist so insert it and then run the satSensorMap query again
                if (!$satSensorResult || $satSensorResult->num_rows === 0) {
                    $satSensorInsert = executeInsert("INSERT into satsensormap (satelliteID, sensorID) values ($satelliteID, $sensorID)");
                    if ($satSensorInsert) {
                        $satSensorResult = executeSelect("SELECT * FROM satsensormap WHERE satelliteID=$satelliteID AND sensorID=$sensorID");
                    } else {
                        echo "error inserting satsensormap INSERT into satsensormap (satelliteID, sensorID) values ($satelliteID, $sensorID)";
                    }
                }
                $row1 = mysqli_fetch_array($satSensorResult);
                $satSensorMapId = $row1['satSensorMapID'];

                //insert the parameterID and satSensorMapID into the edr table
                $sqlInsert = "INSERT into edr (satSensorMapID, parameterID) values ('$satSensorMapId','$paramID')";
                if (!executeInsert($sqlInsert)) {
                    echo "Error inserting edr ";
                    echo "sensor: " . $sensorID . ' sat: ' . $satelliteID . ' edr: ' . $edrDescription;
                }
            } else {
                echo "Error selecting satellite " . mysqli_error();
            }
        } else {
            echo "Error selecting sensor " . mysqli_error();
        }
        //echo '<br>';        
    }
}

function getFiscalYearArray() {
    $curYear = date('Y');
    $array = array($curYear, $curYear + 1, $curYear + 2, $curYear + 3, $curYear + 4, $curYear + 5, $curYear + 6);
    return $array;
}

/**
 * Returns an array of all Fiscal Years that contain at least 1 project
 * @return type
 */
function getProjectFiscalYearArray() {
    $array = array();
    $fyResult = executeSelect("SELECT DISTINCT fy FROM project ORDER BY fy");
    if ($fyResult) {
        while ($row = mysqli_fetch_array($fyResult)) {
            $fy = $row['fy'];
            array_push($array, $fy);
        }
    }
    return $array;
}

//DELETE************************************************************************

/**
 * Delete data for the given table
 * @param type $tableName
 */
function deleteAllData($tableName, $idField = NULL, $idValue = NULL) {
    if (!$idField) {
        $sqlCmd = "DELETE from $tableName";
    } else {
        $sqlCmd = "DELETE from $tableName WHERE $idField=$idValue";
    }
    if (!executeSelect($sqlCmd)) {
        echo "Delete table $tableName failed <br>";
        echo $sqlCmd . '<br>';
    }
}

function numSubtasks($taskID) {
    $subtaskResult = executeSelect("SELECT subtaskId FROM subtask WHERE taskId=$taskID");
    if ($subtaskResult) {
        return $subtaskResult->num_rows;
    }
    return 0;
}

function processDeleteTask($taskId) {
    $subtaskResult = executeSelect("SELECT subtaskId FROM subtask WHERE taskId=$taskId");
    if ($subtaskResult) {
        while ($row = mysqli_fetch_array($subtaskResult)) {
            $subtaskId = $row['subtaskId'];
            if (!deleteSubtask($subtaskId)) {
                echo 'Error deleting subtaskLabor <br>';
            }
        }
        $wbsResult = executeInsert("SELECT wbsId FROM task WHERE taskId=$taskId");
        if ($wbsResult) {
            $row = mysqli_fetch_array($wbsResult);
            $wbsId = $row['wbsId'];
            $taskDelete = executeInsert("DELETE FROM task WHERE taskId=$taskId");
            if ($taskDelete) {
                $wbsDelete = executeInsert("DELETE FROM wbs WHERE wbsId='$wbsId'");
                if ($wbsDelete) {
                    return true;
                } else {
                    echo "error deleting wbs <br>";
                    return false;
                }
            }
        } else {
            echo "error selecting wbs <br>";
        }
    }
}

function deleteProject($projectId) {
    //start with tasks
    $tasks = executeSelect("SELECT * FROM task WHERE projectID=$projectId");
    while ($row = mysqli_fetch_array($tasks)) {
        $taskID = $row['taskID'];
        $taskWbs = $row['wbsID'];
        //now subtasks
        $subtasks = executeSelect("SELECT * FROM subtask WHERE taskID=$taskID");
        while ($row2 = mysqli_fetch_array($subtasks)) {
            $subtaskID = $row2['subtaskID'];
            $subWbs = $row2['wbsID'];
            executeDelete("DELETE FROM notes WHERE wbsID=$subWbs");
            executeDelete("DELETE FROM subtaskfinancial WHERE subtaskID=$subtaskID");
            executeDelete("DELETE FROM subtasklabor WHERE subtaskID=$subtaskID");
            executeDelete("DELETE FROM subtaskdependency WHERE subtaskID=$subtaskID");
            //TO_DO also other subtasks dependent on us?
            executeDelete("DELETE FROM subtask WHERE subtaskID=$subtaskID");
        }
        //delete task stuff now
        executeDelete("DELETE FROM notes WHERE wbsID=$taskWbs");
        executeDelete("DELETE FROM task WHERE taskID=$taskID");
    }
    //delete project stuff
    executeDelete("DELETE FROM projectpersonnel WHERE projectID=$projectId");
    executeDelete("DELETE FROM nonlaborcost WHERE projectID=$projectId");
    executeDelete("DELETE FROM contract WHERE projectID=$projectId");
    executeDelete("DELETE FROM projectreport WHERE projectID=$projectId");
    executeDelete("DELETE FROM project WHERE projectID=$projectId");
    executeDelete("DELETE FROM wbs WHERE projectID=$projectId");
    executeDelete("DELETE FROM multiyearproject WHERE projectID=$projectId");
}

/**
 * Deletes subtask, task and project data
 */
function clearProjects() {
    deleteAllData("notes");
    deleteAllData("subtaskfinancial");
    deleteAllData("subtasklabor");
    deleteAllData("subtaskdependency");
    deleteAllData("subtask");
    deleteAllData('taskparammap');
    deleteAllData('taskreqmap');
    deleteAllData("task");
    deleteAllData("projectpersonnel");
    deleteAllData("nonlaborcost");
    deleteAllData("contract");
    deleteAllData("projectreport");
    deleteAllData("project");
    deleteAllData("wbs");
    deleteAllData('multiyearproject');
}

/**
 * Deletes edr, sat, sensor, subtask, task and project data
 */
function clearDatabase() {
    clearProjects();
    deleteAllData("reqedrmap");
    deleteAllData("edr");
    deleteAllData("satellite");
    deleteAllData("sensor");
    deleteAllData("wbs");
    deleteAllData("requirement");
}

//INSERT************************************************************************
/**
 * Generic insert function. Uses class variables for mysqli username, password, host, and dbName.
 * @global string $username
 * @global string $password
 * @global string $host
 * @global string $dbname
 * @param type $sqlInsert The mysqli insert command
 * @return type True if successful, false if failure
 */
function executeInsert($sqlInsert) {
    return executeSelect($sqlInsert, true);
}

function executeDelete($sqlInsert) {
    return executeSelect($sqlInsert);
}

function executeUpdate($sqlInsert) {
    return executeSelect($sqlInsert);
}

/**
 * Test function
 * @param type $result
 */
function printResults($result) {
    while ($row = mysqli_fetch_row($result)) {
        echo $row[0];
        echo $row[1];
    }
}

function getPersonIDSingleField($field, $value) {
    //PERSON TABLE
    //see if the personID exist if not insert them
    $sqlSelect = "SELECT personId FROM person WHERE $field='$value'";
    $result = executeSelect($sqlSelect);
    if ($result && $result->num_rows > 0) {
        //exists
        $row = mysqli_fetch_array($result);
        $personId = $row[0];
    } else {
        //does not exist create them
        $sqlInsert = "INSERT into person (name) values ('$value')";
        $result = executeInsert($sqlInsert);
        //echo "$sqlInsert <br>";
        if ($result) {
            //get their personId
            $sqlSelect = "SELECT personId FROM person WHERE $field='$value'";
            $result = executeSelect($sqlSelect);
            if ($result && $result->num_rows > 0) {
                //exists
                $row = mysqli_fetch_array($result);
                $personId = $row[0];
            } else {
                echo 'Error selecting performer from person';
            }
        } else {
            echo 'Error inserting performer in person';
        }
    }
    return $personId;
}

/**
 * 
 * @param type $projectId
 * @param type $subTaskId
 * @param type $performers
 * @param type $laborHours
 * @param type $rates
 */
function addPerformersToSubTask($projectId, $subTaskId, $performers, $laborHours, $rates) {

    for ($i = 0; $i < sizeof($performers); $i++) {

        $personId = getPersonIDSingleField("name", $performers[$i]);

        //PROJECT PERSONNEL TABLE
        //see if performer is already in project personnel for this project otherwise insert them
        $sqlSelect = "SELECT projectPersonnelId FROM projectpersonnel WHERE personId=$personId AND
                      role='performer' AND projectId=$projectId";
        $result = executeSelect($sqlSelect);
        if ($result && $result->num_rows > 0) {
            //exists
            $row = mysqli_fetch_array($result);
            $projectPersonnelId = $row[0];
        } else {
            //does not exist create them
            $sqlInsert = "INSERT into projectpersonnel (personId, projectId, role) values 
                                                       ($personId,$projectId,'performer')";
            $result = executeInsert($sqlInsert);
            if ($result) {
                //get their personId
                $sqlSelect = "SELECT projectPersonnelId FROM projectpersonnel WHERE personId=$personId AND
                      role='performer' AND projectId=$projectId";
                $result = executeSelect($sqlSelect);
                if ($result && $result->num_rows > 0) {
                    //exists
                    $row = mysqli_fetch_array($result);
                    $projectPersonnelId = $row[0];
                } else {
                    echo 'Error selecting performer from projectPersonnel';
                }
            } else {
                echo 'Error inserting performer in projectPersonnel';
            }
        }

        //SUBTASK LABOR TABLE
        //now add to subtask labor
        $sqlInsert = "INSERT into subtasklabor (subtaskId, projectPersonnelId, hours, rate) values 
                                               ($subTaskId, $projectPersonnelId, $laborHours[$i], $rates[$i])";
        $result = executeInsert($sqlInsert);
        if (!$result) {
            echo 'Error Inserting subtask labor';
        } else {
            //echo $sqlInsert . '<br>';
        }
    }
}

function removePerformersFromSubTask($projectId, $subTaskId) {

    //SUBTASK LABOR TABLE
    $sqlDelete = "DELETE FROM subtasklabor WHERE subtaskId=$subTaskId";
    $result = executeInsert($sqlDelete);
    if (!$result) {
        echo 'Error Deleting subtask labor';
    }
    //echo $sqlDelete . '<br>';
    //later we made need to query all people on the project and remove the onee (performers)
    //that were only working on this subtask. Right now we do not really need to do this
}

function getFiscalYearFromReqID($reqID) {
    $reqResult = executeSelect("SELECT * FROM requirement WHERE reqID=$reqID");

    //get the lowest priority in this list
    if ($reqResult) {
        $row = mysqli_fetch_array($reqResult);
        $fy = $row['fiscalYear'];
        return $fy;
    } else {
        return NULL;
    }
}

function getDefaultPriority($fy) {
    //get all of the requirements for the given fy
    $reqResult = executeSelect("SELECT * FROM requirement WHERE fiscalYear='$fy' ORDER by priority DESC");

    //get the lowest priority in this list
    if ($reqResult) {
        $row = mysqli_fetch_array($reqResult);
        $lowestPriority = $row['priority'];
        return $lowestPriority + 1;
    } else {
        return -1;
    }
    //return the lowest priority +1
}

function isYearDifferent($fy, $reqId) {
    //get the current fy for this req
    $reqResult = executeSelect("SELECT * FROM requirement WHERE reqID='$reqId'");
    if ($reqResult) {
        $row = mysqli_fetch_array($reqResult);
        $currentFy = $row['fiscalYear'];
        if (strcmp($currentFy, $fy) === 0) {
            return false;
        } else {
            //the year has changed
            return true;
        }
    }
}

function addNewReq($reqDesc, $parameterIdArray, $source, $fiscalYear) {
    //create the parameter
    //If a requirement with this description exists then we don't need to insert
    $reqQuery = "SELECT reqID FROM requirement WHERE description='$reqDesc'";
    $reqResult = executeSelect($reqQuery);
    if (!$reqResult || $reqResult->num_rows === 0) {
        //set default priority here
        $priority = getDefaultPriority($fiscalYear);
        $reqInsert = "INSERT into requirement (description, source, fiscalYear, priority) values ('$reqDesc', '$source', '$fiscalYear', '$priority')";
        //echo $reqInsert;
        $reqInsertResult = executeInsert($reqInsert);
        if (!$reqInsertResult) {
            return false;
        }
        $reqQuery = "SELECT reqID FROM requirement WHERE description='$reqDesc'";
        $reqResult = executeSelect($reqQuery);
    }

    //requirement is now created so relate the req to the selected parameters
    if ($reqResult && $parameterIdArray) {
        $row = mysqli_fetch_array($reqResult);
        $reqID = $row['reqID'];
        //relate this req to each paramter
        for ($i = 0; $i < sizeof($parameterIdArray); $i++) {
            $curParameterId = $parameterIdArray[$i];
            $reqInsert = "INSERT into reqparametermap (reqID, parameterID) values ($reqID,$curParameterId)";
            $reqInsertResult = executeInsert($reqInsert);
        }
        //echo '<h2>Successfully Created the requirement</h2><br>';
        //echo '<a href="editDB.php?reqEdit=1">Back to Add/Edit Requirements</a>';
    }
    return true;
//    else {
//        echo 'error selecting reqId ' . $reqResult . '<br>';
//    }
}

//UPDATE************************************************************************

function updateStatus($status, $type, $id) {
    if (strcmp($type, "project") === 0) {
        $sqlUpdate = "UPDATE project SET status='$status' WHERE projectID=$id";
        return executeInsert($sqlUpdate);
    } else if (strcmp($type, "task") === 0) {
        $sqlUpdate = "UPDATE task SET status='$status' WHERE taskID=$id";
        return executeInsert($sqlUpdate);
    } else if (strcmp($type, "subtask") === 0) {
        $sqlUpdate = "UPDATE subtask SET status='$status' WHERE subtaskID=$id";
        return executeInsert($sqlUpdate);
    }
}

/**
 * This will update the wbsString for the project, tasks and subtasks
 * @param type $projectID the db projectID
 * @param type $fy fy for this project
 * @param type $newProjectWbs the old wbs
 * @param type $oldProjectWbs the wbs we are updating to
 * @return boolean
 */
function updateProjectWbsStructure($projectID, $fy, $newProjectWbs, $oldProjectWbs) {
    //wbs dependent tables: wbs, notes, project, subtask, task
    $endIndex = strpos($newProjectWbs, ".");
    $level1Wbs = substr($newProjectWbs, 0, $endIndex);
    //echo "Old: $oldProjectWbs <br>";
    //echo "New: $newProjectWbs <br>";
    //echo "$level1Wbs<br>";
    //wbs (this will update project because it references the wbsID field)
    $wbsUpdateResult = executeInsert("UPDATE wbs SET wbsString='$newProjectWbs' WHERE wbsString='$oldProjectWbs' and fy='$fy'");
    if ($wbsUpdateResult) {
        //tasks
        $taskResult = executeInsert("SELECT * FROM task WHERE projectID=$projectID");
        if ($taskResult) {
            if (!updateTaskWbsStructure($level1Wbs, $taskResult, $fy)) {
                return false;
            }
        }
    } //end wbs update
    else {
        echo 'Error updating wbs <br>';
        return false;
    }
    return true;
}

function updateTaskWbsStructure($level1Wbs, $result, $fy) {
    //for each task
    while ($row = mysqli_fetch_array($result)) {

        $taskID = $row['taskID'];
        $taskWbsID = $row['wbsID'];
        $taskWbsString = getFieldGivenAnID("wbs", "wbsID", $taskWbsID, "wbsString");
        $newtaskWbsString = swapLevel1Wbs($taskWbsString, $level1Wbs);
        //echo "$newtaskWbsString <br>";
        $wbsUpdateResult = executeInsert("UPDATE wbs set wbsString='$newtaskWbsString' WHERE wbsString='$taskWbsString' AND $fy='$fy'");
        if ($wbsUpdateResult) {
            //subtasks
            $subtaskResult = executeInsert("SELECT * FROM subtask WHERE taskID=$taskID");
            if ($subtaskResult) {
                if (!updateSubtaskWbsStructure($level1Wbs, $subtaskResult, $fy)) {
                    return false;
                }
            } else {
                return false;
            }
        } else {
            return false;
        }
    } //end task update
    return true;
}

function updateSubtaskWbsStructure($level1Wbs, $result, $fy) {
    //for each task
    while ($row = mysqli_fetch_array($result)) {
        //$subtaskID = $row['subtaskID'];
        $subtaskWbsID = $row['wbsID'];
        $subtaskWbsString = getFieldGivenAnID("wbs", "wbsID", $subtaskWbsID, "wbsString");
        $newsubtaskWbsString = swapLevel1Wbs($subtaskWbsString, $level1Wbs);
        //echo "$newsubtaskWbsString <br>";
        //update the wbs
        $wbsUpdateResult = executeInsert("UPDATE wbs set wbsString='$newsubtaskWbsString' WHERE wbsString='$subtaskWbsString' AND $fy='$fy'");
        if (!$wbsUpdateResult) {
            return false;
        }
    }//end subtask update
    return true;
}

function updateFiscalYearAllocated($fy) {
    $fyProjectsResult = executeSelect("SELECT * from project WHERE fy=$fy");
    $cost = 0;
    while ($row = mysqli_fetch_array($fyProjectsResult)) {
        $cost += $row['approvedCost'];
    }
    $updateResult = executeSelect("UPDATE fiscalyear SET totalAllocated=$cost");
    if ($updateResult) {
        return true;
    }
    return false;
}

//SELECT************************************************************************

function getProjectIdForGivenWbsID($wbsId) {
    $projectId = "-1";
    //this could be project, task or subtask so try until we get something
    $projectResult = executeSelect("SELECT projectID FROM project WHERE wbsID='$wbsId'");
    if ($projectResult && $projectResult->num_rows > 0) {
        $row = mysqli_fetch_array($projectResult);
        $projectId = $row['projectID'];
    } else {
        $taskResult = executeSelect("SELECT p.projectID FROM project p, task t WHERE t.projectID=p.projectID AND t.wbsID='$wbsId'");
        if ($taskResult && $taskResult->num_rows > 0) {
            $row = mysqli_fetch_array($taskResult);
            $projectId = $row['projectID'];
        } else {
            $subtaskResult = executeSelect("SELECT p.projectID FROM project p, task t, subtask st WHERE t.projectID=p.projectID AND t.taskID=st.taskID AND st.wbsID='$wbsId'");
            if ($subtaskResult && $subtaskResult->num_rows > 0) {
                $row = mysqli_fetch_array($subtaskResult);
                $projectId = $row['projectID'];
            } else {
                echo 'epic fail';
            }
        }
    }
    return $projectId;
}

function getAllocatedFundingData($fy = NULL) {
    if ($fy) {
        return executeSelect("SELECT * FROM fiscalyear WHERE year=$fy ORDER BY year");
    } else {
        return executeSelect("SELECT * FROM fiscalyear ORDER BY year");
    }
}

function getActiveProjectsData() {
    return executeSelect("SELECT * FROM project WHERE status=\"approved\"");
}

/**
 * Gets the project data for all projects meeting the FY
 * @param type $fy the fiscal year
 * @param type $status if status is given then only projects with that status and fy will be returned
 * @param type $not if this is true then all projects that do not have the $status will be returned
 * @return type
 */
function getProjectsData($fy, $status = NULL, $not = NULL) {
    if ($not) {
        return executeSelect("SELECT * FROM project WHERE fy=$fy AND status!='$status'");
    } else if ($status) {
        return executeSelect("SELECT * FROM project WHERE fy=$fy AND status='$status'");
    } else {
        return executeSelect("SELECT * FROM project WHERE fy=$fy");
    }
}

/**
 * Returns the info for each project that is in a pre approval status (aka propossed project)
 * @param type $fy if given then only proposals for that fy will be returned
 * @param type $status If given then only proposals with that status will be returned
 * @return type
 */
function getProposalsData() {
    return executeSelect("SELECT * FROM project WHERE status='created' OR status='taskSubmit' OR 
                                                      status='subtaskSubmit' OR status='submitComplete' OR 
                                                      status='piFeedback' OR status='piApproverSubmit' ORDER BY fy");
}

function getApprovedProjectsData() {
    return executeSelect("SELECT * FROM project WHERE status!='created' AND status!='taskSubmit' AND 
                                                      status!='subtaskSubmit' AND status<>'submitComplete' AND 
                                                      status!='piFeedback' AND status!='piApproverSubmit' ORDER BY fy");
}

function getAgencyCheckboxList() {
    $allSensorsResult = getAllFieldsInTable("spaceagency", "acronym");
    $counter = 0;
    while ($row = mysqli_fetch_array($allSensorsResult)) {
        $acronym = $row['acronym'];
        $spaceagencyID = $row['spaceAgencyID'];
        echo "<input name =\"checkbox$counter\" value = \"$spaceagencyID\"type=\"checkbox\" id =\"checkbox$counter\"/>$acronym<br />";
        $counter++;
    }
    //echo '</div>';
}

function getPlatformCheckboxList() {
    $allSensorsResult = getAllFieldsInTable("satellite", "acronym");
    $counter = 0;
    while ($row = mysqli_fetch_array($allSensorsResult)) {
        $acronym = $row['acronym'];
        $satID = $row['satelliteID'];
        echo "<input name =\"checkbox$counter\" value = \"$satID\"type=\"checkbox\" id =\"checkbox$counter\"/>$acronym<br />";
        $counter++;
    }
    //echo '</div>';
}

function getSensorCheckboxList() {
    $allProgrammesResult = getAllFieldsInTable("sensor", "acronym");
    $counter = 0;
    while ($row = mysqli_fetch_array($allProgrammesResult)) {
        $acronym = $row['acronym'];
        $sensorID = $row['sensorID'];
        echo "<input name =\"checkbox$counter\" value = \"$sensorID\"type=\"checkbox\" id =\"checkbox$counter\"/>$acronym<br />";
        $counter++;
    }
}

function getHardwareStatusSelections() {
    return executeSelect("SELECT hardwareStatusID, status FROM hardwarestatus ORDER by status");
}

function getSpaceAgencies() {
    return executeSelect("SELECT spaceAgencyID, acronym FROM spaceagency ORDER by acronym");
}

function getSatProgrammes() {
    return executeSelect("SELECT * FROM satelliteprogramme");
}

function getSatSensorsForGivenParameter($paramId) {
    $sqlQuery = "SELECT s.sensorID, s.name AS sensorName, sat.satelliteID, sat.name AS satName
                         FROM satellite sat, sensor s, satsensormap map, edr e 
                         WHERE e.parameterID=$paramId AND e.satSensorMapID=map.satSensorMapID AND
                               map.satelliteID=sat.satelliteID AND map.sensorID=s.sensorID";
    return executeSelect($sqlQuery);
}

/**
 * Returns the name of the person given the project and role.
 * @param type $projectID
 * @param type $role
 * @return type
 */
function getNameForGivenProjectAndRole($projectID, $role) {
    $sqlSelect = "SELECT p.name FROM projectpersonnel pp, person p 
                  WHERE pp.projectID=$projectID AND p.personID=pp.personID AND pp.role='$role'";
    $result = executeSelect($sqlSelect);
    if ($result) {
        $row = mysqli_fetch_array($result);
        return $row['name'];
    } else {
        echo "error";
    }
}

/**
 * Returns the personID for the given email address. If they are not found then they are created and the
 * person ID is returned.
 * @param type $personName
 * @param type $personPhone
 * @param type $personEmail
 * @return type
 */
function getPersonId($personName, $personPhone, $personEmail) {
    $sqlSelect = "SELECT personID from person WHERE email='$personEmail'";
    $result = executeSelect($sqlSelect);
    //echo $sqlSelect.'<br>';
    if ($result && $result->num_rows > 0) {
        $row = mysqli_fetch_array($result);
        //echo $row['personID'];
        return $row['personID'];
    } else {
        //this person is not in the db so let's insert them
        $sqlInsert = "INSERT into person (name, email, phone) values ('$personName', '$personEmail', '$personPhone')";
        $result = executeInsert($sqlInsert);
        //echo $sqlInsert . '<br>';
        if ($result) {
            //now return the id os the person we just inserted
            $sqlSelect = "SELECT personID from person WHERE email='$personEmail'";
            $result = executeSelect($sqlSelect);
            if ($result) {
                $row = mysqli_fetch_array($result);
                return $row['personID'];
            }
        } else {
            echo 'Error inserting person ' . $sqlInsert . '<br>';
        }
    }
}

function getPersonNameFromID($personID) {
    $sqlSelect = "SELECT name from person WHERE personID=$personID";
    $result = executeSelect($sqlSelect);
    //echo $sqlSelect.'<br>';
    if ($result && $result->num_rows > 0) {
        $row = mysqli_fetch_array($result);
        //echo $sqlSelect;
        echo $row['name'];
    } else {
        echo 'Error';
    }
}

function getPersonNameStringFromID($personID) {
    $sqlSelect = "SELECT name from person WHERE personID=$personID";
    $result = executeSelect($sqlSelect);
    //echo $sqlSelect.'<br>';
    if ($result && $result->num_rows > 0) {
        $row = mysqli_fetch_array($result);
        //echo $sqlSelect;
        return $row['name'];
    } else {
        return 'Error';
    }
}

function getEmailFromUsername($username) {
    $personID = getPersonIDFromUsername($username);
    $result = executeSelect("SELECT email FROM person WHERE personID=$personID");
    $row = mysqli_fetch_array($result);
    return $row['email'];
}

/**
 * Returns the personID for the given username (user table)
 * @param type $username
 * @return type
 */
function getPersonIDFromUsername($username) {
    $sqlSelect = "SELECT * from user WHERE username='$username'";
    $result = executeSelect($sqlSelect);
    //echo $sqlSelect . '<br>';
    if ($result) {
        $row = mysqli_fetch_array($result);
        //return getPersonId(NULL, NULL, $email);
        return $row['personID'];
    } else {
        echo 'Error';
    }
}

/**
 * Returns the number of rows in the given table
 * @param type $tableName
 * @return int num rows
 */
function getNumberOfRows($tableName) {
    $sqlSelect = "SELECT COUNT(*) as id FROM " . $tableName . "";
    $counter = executeSelect($sqlSelect);
    if ($counter) {
        $num = mysqli_fetch_array($counter);
        $count = $num["id"];
        return $count;
    } else {
        return 0;
    }
}

/**
 * Generic select function. Uses class variables for mysqli username, password, host, and dbName.
 * @global string $username
 * @global string $password
 * @global string $host
 * @global string $dbname
 * @param type $sqlSelect
 * @return type Returns a result resource (can be passed to mysqli_fetch_assoc) on success, false for a failure.
 */
function executeSelect($sqlSelect, $returnNewID = NULL) {

    global $username, $password, $host, $dbname;
    //echo "user: $username <br> pass: $password <br> host: $host <br> dbName: $dbname<br>";
    //echo $currentDir . '<br>';
    $mysqli = new mysqli("$host", "$username", "$password", "$dbname");
    if ($mysqli->connect_errno) {
        printf("Connect failed: %s\n", $mysqli->connect_error);
        //exit();
    }
    $result = $mysqli->query($sqlSelect);
    if ($result) {
        //printf("Select returned %d rows.\n", $result->num_rows);
        //$result->close();
        if ($returnNewID) {
            return $mysqli->insert_id;
        } else {
            //echo 'Returning result';
            //echo $result->num_rows;
            //echo $sqlSelect;
            return $result;
        }
    } else {
        echo "sql query failed: ' . $mysqli->error . '<br> $sqlSelect <br> ";
        return $result;
    }
}

/**
 * This will return a result set (n rows with edrID, reqID, sutaskID, subTaskTitle) for the given projectID 
 * @param type $projectID
 */
function getReqEdrSubtaskForProject($projectID) {
    $sqlSelect = "SELECT s.edrID, s.reqID, s.subtaskID, s.title FROM project p, task t, subtask s WHERE p.projectID=t.projectID AND t.taskID=s.taskID";
    executeSelect($sqlSelect);
}

/**
 * Gets these fields
 * @param type $projectID
 * @return type
 */
function getSensorSatReqSubtaskEdrForProject($projectID) {
    $sqlSelect = "SELECT e.sensorID, e.satelliteID, s.reqID, s.subtaskID, s.edrID FROM project p, task t, subtask s, edr e 
                  WHERE p.projectID=t.projectID AND 
                        p.projectID=$projectID AND
                        t.taskID=s.taskID AND 
                        s.edrID=e.edrID";

    if (isset($_SESSION['reqSortField'])) {
        $sortOrder = $_SESSION['reqSortOrder'];
        $sortField = $_SESSION['reqSortField'];
        $sqlSelect = $sqlSelect . " ORDER by $sortField $sortOrder";
    }
    //echo $sqlSelect . '<br>';
    $result = executeSelect($sqlSelect);
    if ($result) {
        return $result;
    } else {
        echo "Fail $sqlSelect <br>";
    }
}

/**
 * Returns a mysl result of all fields in a given table who's given idValue matches the given idName
 * @param type $tableName
 * @param type $idName
 * @param type $idValue
 * @return null
 */
function getAllFieldsInTableGiveAnID($tableName, $idName, $idValue) {
    if (strcmp($tableName, "project") == 0) {
        if (isset($_SESSION['projectSortField'])) {
            $sortOrder = $_SESSION['projectSortOrder'];
            $sortField = $_SESSION['projectSortField'];
            $sqlQuery = "SELECT * FROM $tableName WHERE $idName=$idValue ORDER by $sortField $sortOrder";
        } else {
            $sqlQuery = "SELECT * FROM $tableName WHERE $idName=$idValue";
        }
    } else if (strcmp($tableName, "task") == 0) {
        if (isset($_SESSION['taskSortField'])) {
            $sortOrder = $_SESSION['taskSortOrder'];
            $sortField = $_SESSION['taskSortField'];
            $sqlQuery = "SELECT * FROM $tableName WHERE $idName=$idValue ORDER by $sortField $sortOrder";
        } else {
            $sqlQuery = "SELECT * FROM $tableName WHERE $idName=$idValue";
        }
    } else if (strcmp($tableName, "req") == 0) {
        if (isset($_SESSION['reqSortField'])) {
            $sortOrder = $_SESSION['reqSortOrder'];
            $sortField = $_SESSION['reqSortField'];
            $sqlQuery = "SELECT * FROM $tableName WHERE $idName=$idValue ORDER by $sortField $sortOrder";
        } else {
            $sqlQuery = "SELECT * FROM $tableName WHERE $idName=$idValue";
        }
    } else if (strcmp($tableName, "nonlaborcost") == 0) {
        if (isset($_SESSION['nonLaborCostSortField'])) {
            $sortOrder = $_SESSION['nonLaborCostSortOrder'];
            $sortField = $_SESSION['nonLaborCostSortField'];
            $sqlQuery = "SELECT * FROM $tableName WHERE $idName=$idValue ORDER by $sortField $sortOrder";
        } else {
            $sqlQuery = "SELECT * FROM $tableName WHERE $idName=$idValue";
        }
    } else if (strcmp($tableName, "contract") == 0) {
        if (isset($_SESSION['contractSortField'])) {
            $sortOrder = $_SESSION['contractSortOrder'];
            $sortField = $_SESSION['contractSortField'];
            $sqlQuery = "SELECT * FROM $tableName WHERE $idName=$idValue ORDER by $sortField $sortOrder";
        } else {
            $sqlQuery = "SELECT * FROM $tableName WHERE $idName=$idValue";
        }
    } else {
        $sqlQuery = "SELECT * FROM $tableName WHERE $idName=$idValue";
    }
    //echo "Query: " . $sqlQuery . '<br>';
    $result = executeSelect($sqlQuery);
    if ($result) {
        //success
        return $result;
    } else {
        //failure
        echo "$sqlQuery query Failed<br>";
        return null;
    }
}

/**
 * Gets all fields in a given table name
 * @param type $tableName
 * @param type $orderByField
 * @return null
 */
function getAllFieldsInTable($tableName, $orderByField = NULL) {

    if (strcmp($tableName, "project") == 0) {
        if (isset($_SESSION['projectSortField'])) {
            $sortField = $_SESSION['projectSortField'];
            $sortOrder = $_SESSION['projectSortOrder'];
            $sqlQuery = "SELECT * FROM $tableName ORDER by $sortField $sortOrder";
        } else {
            $sqlQuery = "SELECT * FROM $tableName";
        }
    } else {
        if ($orderByField) {
            $sqlQuery = "SELECT * FROM $tableName ORDER by $orderByField";
        } else {
            $sqlQuery = "SELECT * FROM $tableName";
        }
    }
    //echo "Query: " . $sqlQuery . '<br>';
    $result = executeSelect($sqlQuery);
    if ($result) {
        //success
        return $result;
    } else {
        //failure
        echo "$sqlQuery query Failed<br>";
        return null;
    }
}

/**
 * This will return a result set (n rows with reqID, description, source) for the given edrID
 * @param type $edrID
 * @return $result a result set, null if not found
 */
function getRequirementsForThisEDR($edrID) {
    $sqlQuery = "SELECT requirement.description 
                 FROM edrReqMap, requirement, edr 
                 WHERE requirement.reqID = edrReqMap.reqID AND
                       edrReqMap.edrID = edr.edrID AND
                       edr.edrID = '$edrID'";
    //echo "Query: " . $sqlQuery;
    //echo '<br>';
    $localResult = executeSelect($sqlQuery);
    if ($localResult) {
        //success
        //$row = mysqli_fetch_assoc($result);
        return $localResult;
    } else {
        //failure
        echo 'Select requirements for a given edrID Failed' . '<br>';
        return null;
    }
}

/**
 * Retrieves all sensor information
 * @return type Returns a result resource (can be passed to mysqli_fetch_assoc) on success, false for a failure.
 */
function getSensors() {
    return executeSelect("SELECT sensorID, acronym FROM sensor ORDER by acronym");
}

/**
 * Retrieves the name of the sensor for the given unique ID
 * @param type $sensorID
 * @return string
 */
function getSensorName($sensorID) {
    $result = executeSelect("SELECT name FROM sensor WHERE sensorID=$sensorID");
    if ($result) {
        $row = mysqli_fetch_row($result);
        return $row[0];
    } else {
        return "Bad sensor name";
    }
}

/**
 * sensorName in, sensorID out
 * @param type $sensorName
 * @return string
 */
function getSensorID($sensorName) {
    $result = executeSelect("SELECT sensorID FROM sensor WHERE name='$sensorName'");
    if ($result) {
        $row = mysqli_fetch_row($result);
        return $row[0];
    } else {
        return "Bad sensor name";
    }
}

function getSensorIDFromAcronym($acronym) {
    // "***$acronym*** ";
    //echo strlen($acronym);
    //echo " <br>";
    $result = executeSelect("SELECT sensorID FROM sensor WHERE acronym='$acronym'");
    if ($result) {
        $row = mysqli_fetch_row($result);
        return $row[0];
    } else {
        return "Bad sensor name";
    }
}

/**
 * Retrieves all satellite information
 * @return type Returns a result resource (can be passed to mysqli_fetch_assoc) on success, false for a failure.
 */
function getSatellites() {
    return executeSelect("SELECT * FROM satellite");
}

/**
 * Returns the satID and acronym ordered by acronym
 * @return type a mysqli result
 */
function getSatellites2() {
    return executeSelect("SELECT satelliteID, acronym FROM satellite ORDER by acronym");
}

function getProgrammes() {
    return executeSelect("SELECT satelliteProgrammeID, acronym FROM satelliteprogramme ORDER by acronym");
}

function getAgencies() {
    return executeSelect("SELECT spaceAgencyID, acronym FROM spaceagency ORDER by acronym");
}

/**
 * Retrieves the personId personName of all people that have the pi role in the user table 
 * @return type
 */
function getPrincipalInvestigators() {
    return executeSelect("SELECT p.personId, p.name FROM user u , person p WHERE role='pi' AND u.personID=p.personID");
}

/**
 * Satellite name in SatelliteID out
 * @param type $satName
 * @return string
 */
function getSatelliteID($satName) {
    $result = executeSelect("SELECT satelliteID FROM satellite WHERE name='$satName'");
    if ($result) {
        $row = mysqli_fetch_row($result);
        return $row[0];
    } else {
        return NULL;
    }
}

function getSatelliteIDFromAcronym($acronym) {
    $result = executeSelect("SELECT satelliteID FROM satellite WHERE acronym='$acronym'");
    if ($result) {
        $row = mysqli_fetch_row($result);
        return $row[0];
    } else {
        return NULL;
    }
}

/**
 * Retrieves the name of the satellite for the given unique ID
 * @param type $satelliteID
 * @return string
 */
function getSatelliteName($satelliteID) {
    $result = executeSelect("SELECT name FROM satellite WHERE satelliteID=$satelliteID");
    if ($result) {
        $row = mysqli_fetch_row($result);
        return $row[0];
    } else {
        return "Bad satellite name";
    }
}

/**
 * SubtaskID in subtaskTitle out
 * @param type $subtaskID
 * @return string
 */
function getSubtaskTitle($subtaskID) {
    $result = executeSelect("SELECT title FROM subtask WHERE subtaskID=$subtaskID");
    if ($result) {
        $row = mysqli_fetch_row($result);
        return $row[0];
    } else {
        return "Bad subtaskID";
    }
}

/**
 * 
 * @param type $fy
 * @param type $subtaskMenu set to true if you just want id, name and priority returned
 * @return type
 */
function getRequirements($fy = NULL, $subtaskMenu = NULL) {
    if ($subtaskMenu) {
        return executeSelect("SELECT reqID, description, priority  FROM requirement WHERE fiscalYear=$fy ORDER by priority");
    }
    if ($fy) {
        return executeSelect("SELECT * FROM requirement WHERE fiscalYear=$fy ORDER by priority");
    } else {
        return executeSelect("SELECT * FROM requirement ORDER by description ASC");
    }
}

/**
 * Returns a dropdown to select other subtasks in the project
 * @param type $projectID
 * @param type $subtaskID if null returns all subtasks associated to the project. If not null then this subtask is excluded from the list (edit).
 * @return type
 */
function getDependencyDropdownText($projectID, $subtaskID = NULL) {
    if ($subtaskID) {
        $subtaskquery = "SELECT st.subtaskID, st.title 
                         FROM subtask st, task t 
                         WHERE t.projectID=$projectID AND t.taskID=st.taskID AND st.subtaskID!=$subtaskID";
    } else {
        $subtaskquery = "SELECT st.subtaskID, st.title FROM subtask st, task t WHERE t.projectID=$projectID AND t.taskID=st.taskID";
    }
    $subtaskResult = executeSelect($subtaskquery);
    return getGenericDropdownMenuText($subtaskResult, 'dependencyDropdown', "98%", false, NULL, 'dependencyDropdown');
}

function getValidRequirementYears() {
    return executeSelect("SELECT DISTINCT fiscalYear FROM requirement");
}

function getMeasuringRelevance() {
    return executeSelect("SELECT measuringRelevanceID, name FROM measuringrelevance ORDER by name ASC");
}

function getParameters($type = NULL) {
    return executeSelect("SELECT parameterID, name FROM parameter ORDER by name ASC");
}

/**
 * Returns the description of the given edrID as a string
 * @param type $edrID
 * @return String null if failure
 */
function getParameterNameFromEdrID($edrID) {
    $sqlQuery = "SELECT p.name from edr e, parameter p WHERE e.edrID='$edrID' AND p.parameterID=e.parameterID";
    $result = executeSelect($sqlQuery);
    if ($result) {
        $row = mysqli_fetch_array($result);
        return $row[0];
    } else {
        return null;
    }
}

function getParameterNameFromID($id) {
    $sqlQuery = "SELECT name from parameter WHERE parameterID='$id'";
    $result = executeSelect($sqlQuery);
    if ($result) {
        $row = mysqli_fetch_array($result);
        return $row[0];
    } else {
        return null;
    }
}

function getEdrIDFromName($edrName) {
    $sqlQuery = "SELECT edrId from edr WHERE description='$edrName'";
    $result = executeSelect($sqlQuery);
    if ($result) {
        $row = mysqli_fetch_array($result);
        return $row[0];
    } else {
        return null;
    }
}

function getHardwareStatusIdFrom($status) {
    //echo "acr: -$acronym- ";
    //echo strlen($acronym);
    //echo " <br>";
    $sqlQuery = "SELECT hardwareStatusID from hardwarestatus WHERE status='$status'";
    $result = executeSelect($sqlQuery);
    if ($result) {
        $row = mysqli_fetch_array($result);
        $id = $row[0];
        //echo "good $id $result->num_rows<br>";
        return $id;
    } else {
        return null;
    }
}

function getProgrammeIdFromName($name) {
    echo "Name: -$name- <br>";
    //echo strlen($acronym);
    //echo " <br>";
    $sqlQuery = "SELECT satelliteProgrammeID from satelliteprogramme WHERE name='$name'";
    $result = executeSelect($sqlQuery);
    if ($result) {
        $row = mysqli_fetch_array($result);
        $id = $row[0];
        //echo "good $id $result->num_rows<br>";
        return $id;
    } else {
        return null;
    }
}

function getSpaceAgencyIdFromAcronym($acronym) {
    //echo "acr: -$acronym- ";
    //echo strlen($acronym);
    //echo " <br>";
    $sqlQuery = "SELECT spaceAgencyID from spaceagency WHERE acronym='$acronym'";
    $result = executeSelect($sqlQuery);
    if ($result) {
        $row = mysqli_fetch_array($result);
        $id = $row[0];
        //echo "good $id $result->num_rows<br>";
        return $id;
    } else {
        return null;
    }
}

/**
 * Returns the description of the given reqID as a string
 * @param type $reqID
 * @return String null if failure
 */
function getRequirementNameFromID($reqID) {
    $sqlQuery = "SELECT description from requirement WHERE reqID='$reqID'";
    $result = executeSelect($sqlQuery);
    if ($result) {
        $row = mysqli_fetch_array($result);
        return $row[0];
    } else {
        return null;
    }
}

function getRequirementIDFromName($reqName) {
    $sqlQuery = "SELECT reqId from requirement WHERE description='$reqName'";
    $result = executeSelect($sqlQuery);
    if ($result) {
        $row = mysqli_fetch_array($result);
        return $row[0];
    } else {
        return null;
    }
}

/**
 * Returns the description of the requirement that is associated with the given edr as a string
 * @param type $edrID
 * @return null if failure
 */
function getRequirementNameFromEdr($edrID) {
    $sqlQuery = "SELECT description from requirement, edr WHERE edr.edrID=$edrID AND edr.reqID=requirement.reqID";
    $result = executeSelect($sqlQuery);
    if ($result) {
        $row = mysqli_fetch_array($result);
        return $row[0];
    } else {
        return null;
    }
}

/**
 * Returna all edrIDs and Descriptions
 * @return type
 */
function getEdrIDandDescription() {
    return executeSelect("SELECT edrID, description FROM edr ORDER BY description");
}

/**
 * Returns all DISTINCT (no duplicates) edrs in the edr table
 * @return type
 */
function getDistinctEdrDescriptions() {
    return executeSelect("SELECT DISTINCT description FROM edr ORDER BY description");
}

/**
 * Retrieves all tasks related to the given unique projectID from the db
 * @param type $projectID
 * @return type Returns a result resource (can be passed to mysqli_fetch_assoc) on success, false for a failure.
 */
function getTasksRelatedToAProject($projectID) {
    if (isset($_SESSION['sortField'])) {
        $sortField = $_SESSION['sortField'];
        $sortOrder = $_SESSION['sortOrder'];
        $sqlQuery = "SELECT * FROM task WHERE projectID=$projectID ORDER by $sortField $sortOrder";
    } else {
        $sqlQuery = "SELECT * FROM task WHERE projectID=$projectID";
    }
    //echo "huh " . $sql;
    return executeSelect($sqlQuery);
}

function getSubtaskCatergoryArray() {
    $array = array();
    array_push($array, "Research");
    array_push($array, "Design");
    array_push($array, "Documentation");
    array_push($array, "Development");
    array_push($array, "Testing");
    array_push($array, "Integration");
    array_push($array, "Transition");
    array_push($array, "Other");
    return $array;
}

/**
 * These need to stay alphabetical
 * @return array
 */
function getGradingCategoryArray() {
    $array = array();
    array_push($array, "Impact");
    array_push($array, "Priority");
    array_push($array, "Programmatic Feasibility");
    array_push($array, "Return on Investment");
    array_push($array, "Risk");
    array_push($array, "Technical Feasibility");
    array_push($array, "Transition");
    return $array;
}

function getGradingScoreArrayOld() {
    $array = array("Excellent", "Above Average", "Average", "Below Average", "Fail");
    return $array;
}

function getGradingScoreArray() {
    $array = array();
    for ($i = 1; $i < 11; $i++) {
        array_push($array, $i);
    }
    return $array;
}

function getNotesTypeArray() {
    $role = getUserRole();
    $array = array();
    if (strcmp("pi", $role) === 0) {
        array_push($array, "PI Notes");
    } else {
        array_push($array, "Feedback");
        array_push($array, "Reviewer Notes");
    }
    return $array;
}

?>
